/*
 * @(#)JxlsUtils.java
 * 2013-4-11 下午02:27:12
 * 
 *
 * Copyright (c) 2018-2028, HangZhou QiYun InfoTech Co.,Ltd. .
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.qyxx.platform.common.utils.jxls;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.reader.ReaderBuilder;
import net.sf.jxls.reader.ReaderConfig;
import net.sf.jxls.reader.XLSReader;
import net.sf.jxls.reader.XLSSheetReader;
import net.sf.jxls.transformer.XLSTransformer;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.xml.sax.SAXException;

import com.google.common.collect.Lists;

/**
 * Jxls工具类
 * 
 * @author gxj
 * @version 1.0 2013-4-11 下午02:27:12
 * @since jdk1.6
 */
@SuppressWarnings("rawtypes")
public class JxlsUtils {

	/**
	 * sheet数据
	 */
	public static final String SHEET_DATA = "sheetData";
	
	/**
	 * 表格数据
	 */
	public static final String BOOK_DATA = "bookData";
	
	/**
	 * 日期单元格类型
	 */
	public static final String DATE_CELL_STYLE = "dateFormat";
	
	/**
	 * 日期单元格格式
	 */
	public static final String DATE_STYLE = "dateStyle";
	
	/**
	 * 默认日期格式
	 */
	public static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd";
	
	/**
	 * 日期格式化对象
	 */
	public static final NewSimpleDateFormat df = new NewSimpleDateFormat(DEFAULT_DATE_FORMAT);
	
	/**
	 * 日期格式缓存
	 */
	public static Map<String,Map<String,String>> dateformats = new HashMap<String,Map<String,String>>();
	
	/**
	 * 名字分隔符
	 */
	public static final String SPLIT_SYMBOL = ".";
	
	/**
	 * 模板标签信息前缀
	 */
	public static final String TAGINFO_PREFIX = "${r\"";
	
	/**
	 * 模板标签信息后缀
	 */
	public static final String TAGINFO_SUFFIX = "\"r}";
	
	/**
	 * 模板标签信息体前缀
	 */
	public static final String TAGINFO_BODY_PREFIX = TAGINFO_PREFIX + "${";
	
	/**
	 * 模板标签信息体后缀
	 */
	public static final String TAGINFO_BODY_SUFFIX = "}" + TAGINFO_SUFFIX;

	/**
	 * 模板foreach标签信息后缀
	 */
	public static final String FOR_TAGINFO_SUFFIX = "</jx:forEach>";
	
	/**
	 * excel单sheet最大行数
	 */
	public static final int ROW_NAMBER_MAX = 65535;
	
	/*
	public static HSSFCellStyle dateCellStyle;
	
	static {
		try {
			HSSFWorkbook workbook = new HSSFWorkbook();
			dateCellStyle = workbook.createCellStyle();
	        HSSFDataFormat format= workbook.createDataFormat();
	        dateCellStyle.setDataFormat(format.getFormat(DEFAULT_DATE_FORMAT));
		} catch(Exception e) {
			e.printStackTrace();
		}
	}*/

	/**
	 * 根据Excel模板及数据，生成Excel导入数据模板
	 * 
	 * @param templateFilePath
	 *            模板路径
	 * @param sheetNameList
	 *            工作簿名称列表
	 * @param sheetDataList
	 *            工作簿数据列表
	 * @param destFilePath
	 * @throws IOException
	 * @throws ParsePropertyException
	 * @throws InvalidFormatException
	 */
	public static void createImportExcelTemplate(
			String templateFilePath, List<String> sheetNameList,
			List<?> sheetDataList, String destFilePath)
															throws IOException,
															ParsePropertyException,
															InvalidFormatException {
		XLSTransformer transformer = new XLSTransformer();
		InputStream is = new BufferedInputStream(new FileInputStream(templateFilePath));
		Workbook workbook = null;
		try {
			Map beanParams = new HashMap();
			preAddSystemParam(beanParams);
			workbook = transformer.transformMultipleSheetsList(
				is, sheetDataList, sheetNameList, SHEET_DATA,
				beanParams, 0);
		} finally {
			IOUtils.closeQuietly(is);
		}
		OutputStream os = new BufferedOutputStream(new FileOutputStream(destFilePath));
		try {
			workbook.write(os);
			os.flush();
		} finally {
	        IOUtils.closeQuietly(os);
		}
	}
	
	/**
	 * 根据导出模板生成Excel导出数据文件
	 * 
	 * @param templateFilePath
	 * @param beanParams
	 * @param destFilePath
	 * @throws IOException
	 * @throws ParsePropertyException
	 * @throws InvalidFormatException
	 */
	public static void createExportExcelEfficient(
			String templateFilePath, 
			Map<String, List> beanParams, String destFilePath)
															throws IOException,
															ParsePropertyException,
															InvalidFormatException {
		//XLSTransformer transformer = new XLSTransformer();
		//InputStream is = new BufferedInputStream(new FileInputStream(templateFilePath));
		Workbook workbook = WorkbookFactory.create(new File(templateFilePath));
		try {
			preAddSystemParam(beanParams);
			System.out.println("transformXLSstart:" + new Date().toString());
			//添加行数据 
			for(String key : beanParams.keySet()) {
				
				//List rows = beanParams.get(key);
				String s = workbook.getSheetName(0);
				System.out.println(s);
				
			}
//			ListbeanParams
//            for(int k=1;k<rows.size()+1;k++){ 
//            List row = rows.get(k-1); 
//            for(int m=0;m<row.size();m++){ 
//            Label lb2 = new Label(m,k,row.get(m).toString()); 
//            ws.addCell(lb2); 
//            } 
//			transformer.transformXLS(templateFilePath, beanParams, destFilePath);
//			System.out.println("transformXLSend:" + new Date().toString());
		} catch(Exception e) {
			System.out.println(e);
		} finally {
//			IOUtils.closeQuietly(is);
		}
//		OutputStream os = new BufferedOutputStream(new FileOutputStream(destFilePath));
//		try {
//			workbook.write(os);
//			os.flush();
//		} finally {
//	        IOUtils.closeQuietly(os);
//		}
	}

	/**
	 * 根据导出模板生成Excel导出数据文件
	 * 
	 * @param templateFilePath
	 * @param beanParams
	 * @param destFilePath
	 * @throws IOException
	 * @throws ParsePropertyException
	 * @throws InvalidFormatException
	 */
	public static void createExportExcel(
			String templateFilePath, 
			Map beanParams, String destFilePath)
															throws IOException,
															ParsePropertyException,
															InvalidFormatException {
		XLSTransformer transformer = new XLSTransformer();
		InputStream is = new BufferedInputStream(new FileInputStream(templateFilePath));
		Workbook workbook = null;
		try {
			preAddSystemParam(beanParams);
			workbook = transformer.transformXLS(is, beanParams);
//			transformer.transformXLS(templateFilePath, beanParams, destFilePath);
		} finally {
			IOUtils.closeQuietly(is);
		}
		OutputStream os = new BufferedOutputStream(new FileOutputStream(destFilePath));
		try {
			workbook.write(os);
			os.flush();
		} finally {
	        IOUtils.closeQuietly(os);
		}
	}
	
	/**
	 * 根据导出模板生成Xls导出数据文件
	 * 
	 * @param templateFilePath
	 * @param beanParams
	 * @param destFilePath
	 * @throws IOException
	 * @throws ParsePropertyException
	 * @throws InvalidFormatException
	 */
	public static void createExportXls(
			String templateFilePath, 
			Map beanParams, String destFilePath)
					throws IOException,
					ParsePropertyException,
					InvalidFormatException {
		InputStream templateis = new BufferedInputStream(new FileInputStream(templateFilePath));
		InputStream is = new BufferedInputStream(new FileInputStream(templateFilePath));
		Workbook templateWorkbook = null;
		Workbook workbook = null;
		try {
			templateWorkbook = WorkbookFactory.create(templateis);
			workbook = getWorkbook(beanParams, is);
		} finally {
			IOUtils.closeQuietly(templateis);
			IOUtils.closeQuietly(is);
		}
		pourIntoWorkbook(templateWorkbook, beanParams, workbook);
		OutputStream os = new BufferedOutputStream(new FileOutputStream(destFilePath));
		try {
			workbook.write(os);
			os.flush();
		} finally {
	        IOUtils.closeQuietly(os);
		}
	}
	
	/**
	 * 将数据根据模版解析，写入Workbook
	 * 
	 */
	private static void pourIntoWorkbook(Workbook templateWorkbook, Map<String, Map<String, Object>> beanParams, Workbook workbook) {
		
		Map<String, Object> allSheetData = beanParams.get(JxlsUtils.BOOK_DATA);
		//DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		for(int i=0; i<allSheetData.size(); i++) {
			Sheet templateSheet = templateWorkbook.getSheetAt(i);
			String sheetKey = templateSheet.getRow(1).getCell(0).getStringCellValue();
			String find = "\\u007BbookData.*\\u007D";
			Pattern pattern = Pattern.compile(find);
	        Matcher matcher = pattern.matcher(sheetKey);
	        if(matcher.find(0)) {
	        	sheetKey = matcher.group(0);
	        }
	        sheetKey = sheetKey.substring(11, sheetKey.length()-3);
			
	        List<Map<String,Object>> sheetDataList = (List<Map<String,Object>>)allSheetData.get(sheetKey);
	        Sheet sheet = workbook.getSheetAt(i);
			Row templateRow = templateWorkbook.getSheetAt(i).getRow(2);
			int rownum = 0;
			int sheetnum = 0;
			Map<Integer, String> cellKeyMap = new HashMap<Integer, String>();
			Iterator<Cell> cells = templateRow.cellIterator();
			while(cells.hasNext()) {
				Cell templateCell = cells.next();
				String templateCellValue = templateCell.getStringCellValue();
				if(templateCellValue != "") {
					if(templateCellValue.indexOf("dateFormat.format") != -1) {
						String key = templateCellValue.substring(30, templateCellValue.length()-2);
						cellKeyMap.put(templateCell.getColumnIndex(), key);
					} else {
						String key = templateCellValue.substring(12, templateCellValue.length()-1);
						cellKeyMap.put(templateCell.getColumnIndex(), key);
					}
				}
			}
			
			for(Map<String,Object> map : sheetDataList) {
				rownum++;
				if(rownum > JxlsUtils.ROW_NAMBER_MAX) {
					sheetnum++;
					sheet = workbook.createSheet(workbook.getSheetName(i) + "_" + sheetnum);
					rownum = 0;
				}
				Row row = sheet.createRow(rownum);
				for(Integer index : cellKeyMap.keySet()) {
					Cell cell = row.createCell(index);
					String key = cellKeyMap.get(index);
					String dtFmt = DEFAULT_DATE_FORMAT;
					String subKey = "id";
					if(StringUtils.contains(key, ",")) {
						String[] keys = StringUtils.split(key, ",");
						key = keys[0];
						String dtFmtKey = keys[1];
						dtFmt = StringUtils.substring(dtFmtKey, 1, dtFmtKey.length()-1);
						/*if(StringUtils.startsWith(dtFmtKey, "dateStyle")) {//日期格式
							String[] dtFmtKeys = StringUtils.split(dtFmtKey, ".");
							if(dtFmtKeys.length >= 3) {
								String formKey = dtFmtKeys[1];
								String fieldKey = dtFmtKeys[2];
								Map<String,String> fmtMap = dateformats.get(formKey);
								if(fmtMap != null) {
									String fmtStr = fmtMap.get(fieldKey);
									if(StringUtils.isNotBlank(fmtStr)) {
										dtFmt = fmtStr;
									}
								}
							}
						}*/
					} else if(StringUtils.contains(key, ".")) {//主表ID
						String[] keys = StringUtils.split(key, ".");
						key = keys[0];
						subKey = keys[1];
					}
					Object o = map.get(key);
					if(o == null) {
						cell.setCellType(Cell.CELL_TYPE_BLANK);
					} else if(o instanceof Date) {
						cell.setCellValue(df.format((Date)o, dtFmt));
					} else if(o instanceof Map) {//获取关联对象ID、其他属性
						Map<String, Object> subMap = (Map<String, Object>)o;
						cell.setCellValue(String.valueOf(subMap.get(subKey)));
					} else {
						cell.setCellValue(o.toString());
					}
				}
			}
		}
	}
	
	/**
	 * 根据模版文件生成空Workbook
	 * @throws InvalidFormatException 
	 * @throws ParsePropertyException 
	 * 
	 */
	private static Workbook getWorkbook(Map<String, Map<String, Object>> beanParams, InputStream is) 
			throws ParsePropertyException, InvalidFormatException  {
		Map<String, Object> nullSheet = new HashMap<String, Object>();
		Map<String, Object> allsheet = beanParams.get(JxlsUtils.BOOK_DATA);
		for(String key : allsheet.keySet()) {
			nullSheet.put(key, new ArrayList());
		}
		Map<String, Object> nullParams = new HashMap<String, Object>();
		nullParams.put(JxlsUtils.BOOK_DATA, nullSheet);
		preAddSystemParam(nullParams);
		XLSTransformer transformer = new XLSTransformer();
		return transformer.transformXLS(is, nullParams);
	}
	
	/**
	 * 解析Excel，将数据存放在Map中返回
	 * 
	 * Map中key为sheet名称，value为sheet数据（List类型）
	 * 
	 * 
	 * @param excelFile excel数据文件
	 * @param importMappingFilePath jxls导入映射文件
	 * @return
	 * @throws IOException
	 * @throws ParsePropertyException
	 * @throws InvalidFormatException
	 * @throws SAXException
	 */
	@SuppressWarnings("unchecked")
	public static Map<String, Object> parseExcelTemplate(
			File excelFile, String importMappingFilePath)
															throws IOException,
															ParsePropertyException,
															InvalidFormatException,
															SAXException {
		Map<String, Object> allData = new HashMap<String, Object>();
		InputStream inputXML = new BufferedInputStream(
				new FileInputStream(new File(importMappingFilePath)));
		XLSReader reader = null;
		try {
			reader = ReaderBuilder.buildFromXML(inputXML);
		} finally {
			IOUtils.closeQuietly(inputXML);
		}
		ReaderConfig.getInstance().setSkipErrors(true);
		InputStream inputXLS = new BufferedInputStream(
				new FileInputStream(excelFile));
		Workbook hssfInputWorkbook = null;
		try {
			hssfInputWorkbook = WorkbookFactory.create(inputXLS);
		} finally {
			IOUtils.closeQuietly(inputXLS);
		}
		
		int num = hssfInputWorkbook.getNumberOfSheets();
		for (int i = 0; i < num; i++) {
			Map moduleData = new HashMap();
			List inputData = Lists.newArrayList();
			moduleData.put(SHEET_DATA, inputData);
			Sheet st = hssfInputWorkbook.getSheetAt(i);
			String name = hssfInputWorkbook.getSheetName(i);
			XLSSheetReader sheetReader = (XLSSheetReader) reader
					.getSheetReaders().get(name);
			sheetReader.read(st, moduleData);
			Object o = moduleData.get(SHEET_DATA);
			allData.put(name, o);
		}
		return allData;
	}
	
	/**
	 * 获取foreach标签体前缀
	 * 
	 * @param items
	 * @return
	 */
	public static String getForTagInfoPrefix(String items) {
		StringBuffer sb = new StringBuffer();
		sb.append(TAGINFO_PREFIX);
		sb.append("<jx:forEach items=\"${");
		sb.append(BOOK_DATA);
		sb.append("['");
		sb.append(items);
		sb.append("']}\" var=\"");
		sb.append(SHEET_DATA);
		sb.append("\" varStatus=\"status\">");
		sb.append(TAGINFO_SUFFIX);
		return sb.toString();
	}
	
	/**
	 * 获取标签信息
	 * 
	 * @param key
	 * @param formkey 表key
	 * @param dateFormatStr 字段key
	 * @return
	 */
	public static String getTagInfo(String key, String formkey, String dateFormatStr) {
		StringBuffer sb = new StringBuffer();
		sb.append(TAGINFO_BODY_PREFIX);
		sb.append(DATE_CELL_STYLE);
		sb.append(SPLIT_SYMBOL);
		sb.append("format(");
		sb.append(SHEET_DATA);
		sb.append(SPLIT_SYMBOL);
		sb.append(key);
		//sb.append(",dateStyle."+formkey+"."+key+")");
		sb.append(",\"" + dateFormatStr + "\")");
		sb.append(TAGINFO_BODY_SUFFIX);
		//将日期格式添加到缓存
		/*if(dateformats.containsKey(formkey)) {
			Map<String, String> m = dateformats.get(formkey);
			m.put(key, dateFormatStr);
			dateformats.put(formkey, m);
		} else {
			Map<String, String> m = new HashMap<String, String>();
			m.put(key, dateFormatStr);
			dateformats.put(formkey, m);
		}*/
		return sb.toString();
	}
	
	/**
	 * 获取标签信息
	 * 
	 * @param key
	 * @param isDate
	 * @return
	 */
	public static String getTagInfo(String key, boolean isDate) {
		StringBuffer sb = new StringBuffer();
		sb.append(TAGINFO_BODY_PREFIX);
		if(isDate) {
			sb.append(DATE_CELL_STYLE);
			sb.append(SPLIT_SYMBOL);
			sb.append("format(");
		}
		sb.append(SHEET_DATA);
		sb.append(SPLIT_SYMBOL);
		sb.append(key);
		if(isDate) {
			sb.append(")");
		}
		sb.append(TAGINFO_BODY_SUFFIX);
		return sb.toString();
	}
	
	/**
	 * 获取非日期标签信息
	 * 
	 * @param key
	 * @return
	 */
	public static String getTagInfo(String key) {
		return getTagInfo(key, false);
	}
	
	/**
	 * 预加入系统参数
	 * 
	 * @param params
	 */
	@SuppressWarnings("unchecked")
	public static void preAddSystemParam(Map params) {
        params.put(DATE_CELL_STYLE, df);
        params.put(DATE_STYLE, dateformats);
	}
	
	public static void main(String[] args) {
		/*String templateFilePath = "E:/bob/03-workspace/jiawasoft/jws-gw/src/main/resources/template/excel/exportTemplate.xls";
		String destFilePath = "E:/out.xls";
		List<String> sheetNameList = Lists.newArrayList();
		sheetNameList.add("主表1");
		sheetNameList.add("子表1");
		List sheetDataList = Lists.newArrayList();
		Form form = new Form();
		form.setIsMaster(true);
		form.setTagInfo("${r\"${sheetData.id}\"}");
		Field field = new Field();
		field.setEnableExport(true);
		field.setKey("name");
		field.setCaption("名次");
		field.setTagInfo("${r\"${sheetData.name}\"}");
		form.getFieldList().add(field);
		sheetDataList.add(form);
		form = new Form();
		form.setIsMaster(false);
		form.setTagInfo("${r\"${sheetData.renyuanguanli.id}\"}");
		field = new Field();
		field.setEnableExport(true);
		field.setKey("guoji");
		field.setCaption("中文");
		field.setTagInfo("${r\"${sheetData.guoji}\"}");
		form.getFieldList().add(field);
		sheetDataList.add(form);
		try {
			JxlsUtils.createImportExcelTemplate(templateFilePath, sheetNameList, sheetDataList, destFilePath);
		} catch (ParsePropertyException e) {
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		
		long systime = Calendar.getInstance().getTimeInMillis() ; 
		System.out.println(systime);
		System.out.println(System.currentTimeMillis());
		long dead = 31535000000l+systime; System.out.println(dead);
		
		try {
			Date date = DateUtils.parseDate("2099-12-31", new String[]{"yyyy-MM-dd"});
			System.out.println(date.getTime());
			
			System.out.println(FileUtils.getTempDirectoryPath());
		} catch (ParseException e) {
			e.printStackTrace();
		}*/
		System.out.println(getForTagInfoPrefix("main.table"));
	}

}
